package com.baobaoyeye.get;
import com.baobaoyeye.conn.*;
import java.sql.*;

public class GetInfo {
	private String SubjectId = null;
	private double avg = 0;
	private int max = 0;
	private int min = 0;
	
	private ConnMySQL connMysql = null;
	
	
	public double getAvg(){
		String sql = "select AVG(count) from sub_stu where sub_id='"+SubjectId+"';";
		ResultSet rs = connMysql.executeQuery(sql);
		try {
			if(rs.next()){
				avg = rs.getDouble(1);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return avg;
		
	}
	
	public int getMax(){
		String sql = "select MAX(count) from sub_stu where sub_id='"+SubjectId+"';";
		ResultSet rs = connMysql.executeQuery(sql);
		try {
			if(rs.next()){
				max = rs.getInt(1);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return max;
	}
	
	public int getMin(){
		String sql = "select MIN(count) from sub_stu where sub_id='"+SubjectId+"';";
		ResultSet rs = connMysql.executeQuery(sql);
		try {
			if(rs.next()){
				min = rs.getInt(1);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return min;
	}
	
	public ResultSet getResultSet(){
		String sql = "select stu_id,count from sub_stu where sub_id='"+SubjectId+"' and count>=85 order by count desc;";
		ResultSet rs = connMysql.executeQuery(sql);
		return rs;
	}
	
	public GetInfo(String teacher_id){
		connMysql = new ConnMySQL();
		SubjectId = GetSubjectId(teacher_id);
	}
	
	private String GetSubjectId(String teacher_id){
		String result = null;
		String sql = "select subject_id from subject where subject_teacher_id='"+teacher_id+"';";
		ResultSet rs0 = connMysql.executeQuery(sql);
		try {
			if(rs0.next()){
				try {
					result = rs0.getString(1);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			rs0.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	
}
